SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TDCANJ_CLIE_Q01]  
/*----------------------------------------------------------------*/  
/*-- Empresa  : OFISIS S.A.      */  
/*-- Cliente  : CASSINELLI      */  
/*-- Sistema  : Tesoreria      */  
/*-- Programa  : Reporte Seguimiento Documentos    */  
/*-- Script  : TDCANJ_CLIE_Q01       */  
/*-- Nombre  : SP_TDCANJ_CLIE_Q01         */  
/*-- Desarrollado : Eduardo Vivar Pomiano       */  
/*-- Fecha  : 07/02/2000       */  
/*-- Base de Datos : MS SQL Server      */  
/*-- Versi½n  : 7.0       */  
/*----------------------------------------------------------------*/  
/*----------------------------------------------------------------*/  
/*-- Modificado por  :        */  
/*-- Fecha modificaci½n  :        */  
/*-- Detalle modificaci½n:        */  
/*----------------------------------------------------------------*/  
/*----------------------------------------------------------------*/  
/* drop proc SP_TDCANJ_CLIE_Q01        */  
/* grant all on SP_TDCANJ_CLIE_Q01 to public     */  
/* SP_TDCANJ_CLIE_Q01 '10', '00000001', '00000001'      */  
/*----------------------------------------------------------------*/  
  
( @ISCO_EMPR TD_VC_002,  
  @ISCO_CLIE TD_VC_020,  
  @ISNU_CANJ TD_VC_008  
)  
 As  
Declare  
 @VSTI_DOCU        TD_VC_003,  
 @VSNU_DOCU_CLIE   TD_VC_020,  
 @VSNU_LETR_CLIE   TD_VC_020,   
 @VINU_SECU        TD_IN_001,  
 @OSTI_DOCU    TD_VC_003  
  
  Create Table #TWCANJ_CLIE  
  (  NU_OPER   VarChar(9)   NULL,  
     NU_SECU        smallint  NULL,    
    TI_DOCU        VarChar(3)   NULL,  
    NU_DOCU_CLIE   VarChar(20)  NULL,   
    TI_DOCU_LETR   VarChar(3)   NULL,  
    NU_LETR_CLIE   VarChar(15)  NULL)   
  
Select @VINU_SECU = 0  
EXEC SP_TTDOCU_CNTB_Q02 6, @OSTI_DOCU  OUTPUT  
  
-- CURSOR CON TODOS LOS DOCUMENTOS DE ESE NUMERO DE CANJE ( DOCUMENTOS ORIGENES )  
  
Declare CU_TDCANJ_CLIE_Q01_1 cursor  
For  
Select CO_TIPO_DOCU, NU_DOCU_CLIE  
From TDCANJ_CLIE  
Where CO_EMPR = @ISCO_EMPR   
And CO_CLIE = @ISCO_CLIE   
And NU_CANJ = @ISNU_CANJ  
And CO_TIPO_DOCU IS NOT NULL   
  
Open CU_TDCANJ_CLIE_Q01_1  
Fetch CU_TDCANJ_CLIE_Q01_1 into @VSTI_DOCU, @VSNU_DOCU_CLIE  
While @@fetch_status = 0  
  Begin  
    If @@fetch_status != 0  
       Begin  
         Raiserror 20001 "Fin de archivo"  
         Close CU_TDCANJ_CLIE_Q01_1  
         Deallocate CU_TDCANJ_CLIE_Q01_1  
         Return   
       end   
  
    Select @VINU_SECU = @VINU_SECU + 1  
  
    Insert Into #TWCANJ_CLIE ( NU_OPER  , NU_SECU, TI_DOCU, NU_DOCU_CLIE)      
    Values ('C'+@ISNU_CANJ, @VINU_SECU, @VSTI_DOCU, @VSNU_DOCU_CLIE)  
  
    Fetch CU_TDCANJ_CLIE_Q01_1 Into @VSTI_DOCU, @VSNU_DOCU_CLIE  
  End  
  
Close CU_TDCANJ_CLIE_Q01_1  
Deallocate CU_TDCANJ_CLIE_Q01_1  
  
Select @VINU_SECU = 0  
  
-- LAS LETRAS DE ESE CANJE CLIENTE ( LETRAS ORIGINADAS POR EL CANJE )  
Declare CU_TDCANJ_CLIE_Q01_1 cursor  
For  
Select @OSTI_DOCU, T1.NU_LETR_CLIE  
From TDCANJ_CLIE T1, TMLETR_CLIE T2  
Where T1.CO_EMPR = @ISCO_EMPR and  
T1.CO_EMPR = T2.CO_EMPR and  
T1.CO_CLIE = @ISCO_CLIE and  
T1.CO_CLIE = T2.CO_CLIE  and  
T1.NU_LETR_CLIE = T2.NU_LETR_CLIE  and  
T1.NU_CANJ = @ISNU_CANJ AND  
T1.NU_LETR_CLIE is not null  
  
Open CU_TDCANJ_CLIE_Q01_1  
Fetch CU_TDCANJ_CLIE_Q01_1 Into @VSTI_DOCU, @VSNU_LETR_CLIE  
While @@fetch_status = 0  
  Begin  
    If @@fetch_status != 0  
       Begin  
         Raiserror 20001 "Fin de archivo"  
         Close CU_TDCANJ_CLIE_Q01_1  
         Deallocate CU_TDCANJ_CLIE_Q01_1  
         Return   
       end   
  
    Select @VINU_SECU = @VINU_SECU + 1  
  
    If NOT Exists ( Select * From #TWCANJ_CLIE Where NU_SECU = @VINU_SECU)  
       Insert into #TWCANJ_CLIE (NU_OPER, NU_SECU, TI_DOCU_LETR, NU_LETR_CLIE)      
       Values ('C'+@ISNU_CANJ, @VINU_SECU, @VSTI_DOCU, @VSNU_LETR_CLIE)  
    Else  
       Update #TWCANJ_CLIE Set TI_DOCU_LETR = @VSTI_DOCU, NU_LETR_CLIE = @VSNU_LETR_CLIE  
       Where NU_SECU = @VINU_SECU    
  
    Fetch CU_TDCANJ_CLIE_Q01_1 into @VSTI_DOCU, @VSNU_LETR_CLIE  
  End  
  
close CU_TDCANJ_CLIE_Q01_1  
deallocate CU_TDCANJ_CLIE_Q01_1  
  
Select NU_OPER, TI_DOCU, NU_DOCU_CLIE, TI_DOCU_LETR, NU_LETR_CLIE   
From #TWCANJ_CLIE  
ORDER BY NU_OPER, NU_SECU  
Return  
  
/*------------------------------ FIN ---------------------------------*/


GO
